import pymysql
from pyquery import PyQuery as pq

head = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36 Edg/112.0.1722.48'
}


def getbookslists(url):
    tlist = []
    doc = pq(url=url, headers=head)
    mylist = doc("table")
    for web in mylist.items():
        leilist = [
            web(".pl2").text().replace("\n", "   ").replace("'", "‘"),  # 书名
            web('p[class^="pl"]').text(),                              # 简介
            web('span[class^="rating_nums"]').text(),                  # 评分
            web('span[class^="pl"]').text()[2:11],                     # 评价人数
            web(".inq").text()                                         # 引用语
        ]
        tlist.append(leilist)
    return tlist


def cunchu(weilist):
    for jun in weilist:
        connect = pymysql.connect(host="localhost", user="root", password="123456", db="spiderwork")
        course = connect.cursor()
        sql = "INSERT INTO 豆瓣图书 values ('%s','%s','%s','%s','%s')" % (jun[0], jun[1], jun[2], jun[3], jun[4])
        try:
            print("正在写入数据！")
            course.execute(sql)
            connect.commit()
        except Exception as e:
            print("数据写入失败！",e)
            connect.rollback()
            connect.close()


for i in range(10):
    url = f"https://book.douban.com/top250?start={25*i}"
    weilist = getbookslists(url)
    cunchu(weilist)
